package com.tisson.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.tisson.database.FormatUtil;
import com.tisson.database.StatementUtil;
import com.tisson.pojo.AcBuilding;
import com.tisson.pojo.Building;

public class AcBuildingDAO {
	private String query;
	private ResultSet rs;

	public List combineQuery(String name, String addr, String district,
			String area) throws SQLException {
		query = "select * from ac_area,ac_building,ac_district ";
		String where = "where ac_district.AreaId = ac_area.AreaId "
				+ "and ac_building.DistrictId = ac_district.DistrictId";
		String a, b, d, e;
		if (addr == null || addr.equals(""))
			a = " and BuildingL like '%'";
		else
			a = " and BuildingL = '" + addr + "'";
		if (name == null || name.equals(""))
			b = " and BuildingName like '%'";
		else
			b = " and BuildingName like '%" + name + "%'";
		if (district == null || district.equals(""))
			d = " and ac_district.DistrictId like '%'";
		else
			d = " and ac_district.DistrictId = '" + district + "'";
		if (area == null || area.equals(""))
			e = " and ac_district.AreaId like '%'";
		else
			e = " and ac_district.AreaId = '" + area + "'";
		query = query + where + a + b + d + e;
		Statement stmt = StatementUtil.getStatement();
		ResultSet rs = stmt.executeQuery(query);
		List accesses = new ArrayList();
		while (rs.next()) {
			Building building = new Building();
			building.setBuildingId(FormatUtil.ConvertUuid(rs
					.getBytes("BuildingId")));
			building.setBuildingName((String) rs.getObject("BuildingName"));
			building.setBuildingLocation((String) rs.getObject("BuildingL"));
			building.setDistrict((String) rs.getObject("DistrictName"));
			building.setArea((String) rs.getObject("AreaName"));
			building.setBuildingComment((String) rs.getObject("BuildingC"));
			accesses.add(building);
		}

		rs.close();
		stmt.close();
		return accesses;
	}

	public AcBuilding findById(String id) throws SQLException {
		query = "select * from ac_building where BuildingId = '" + id + "'";
		Statement stmt = StatementUtil.getStatement();
		rs = stmt.executeQuery(query);
		AcBuilding building = new AcBuilding();
		while (rs.next()) {
			building.setBuildingId(FormatUtil.ConvertUuid(rs
					.getBytes("BuildingId")));
			building.setBuildingName((String) rs.getObject("BuildingName"));
			building.setBuildingLocation((String) rs.getObject("BuildingL"));
			building.setBuildingComment((String) rs.getObject("BuildingC"));
			building.setDistrictId(FormatUtil.ConvertUuid(rs
					.getBytes("DistrictId")));
		}
		rs.close();
		stmt.close();
		return building;
	}

	public List findByDistrictId(String id) throws SQLException {
		List buildingList = new ArrayList();
		query = "select * from ac_building where DistrictId = '" + id + "'";
		Statement stmt = StatementUtil.getStatement();
		rs = stmt.executeQuery(query);
		while (rs.next()) {
			AcBuilding building = new AcBuilding();
			building.setBuildingId(FormatUtil.ConvertUuid(rs
					.getBytes("BuildingId")));
			building.setBuildingName((String) rs.getObject("BuildingName"));
			building.setBuildingLocation((String) rs.getObject("BuildingL"));
			building.setBuildingComment((String) rs.getObject("BuildingC"));
			building.setDistrictId(FormatUtil.ConvertUuid(rs
					.getBytes("DistrictId")));
			buildingList.add(building);
		}
		rs.close();
		stmt.close();
		return buildingList;
	}

	public void save(AcBuilding building) throws SQLException {
		query = "insert into ac_building (BuildingName, BuildingL,BuildingC, DistrictId) values('"
				+ building.getBuildingName()
				+ "','"
				+ building.getBuildingLocation()
				+ "','"
				+ building.getBuildingComment()
				+ "','"
				+ building.getDistrictId() + "')";
		Statement stmt = StatementUtil.getStatement();
		stmt.executeUpdate(query);
		stmt.close();
	}

	public void update(AcBuilding building) throws SQLException {
		query = "update ac_building set " + "BuildingName = '"
				+ building.getBuildingName() + "',BuildingL = '"
				+ building.getBuildingLocation() + "',BuildingC = '"
				+ building.getBuildingComment() + "',DistrictId = '"
				+ building.getDistrictId() + "' where BuildingId = '"
				+ building.getBuildingId() + "'";
		Statement stmt = StatementUtil.getStatement();
		stmt.executeUpdate(query);
		stmt.close();
	}

	public void delete(AcBuilding building) throws SQLException {
		query = "delete from ac_building where BuildingId = '"
				+ building.getBuildingId() + "'";
		Statement stmt = StatementUtil.getStatement();
		stmt.executeUpdate(query);
		stmt.close();
	}
}
